import sys
import time

import pymysql
from apscheduler.schedulers.background import BackgroundScheduler
from pymysql.cursors import DictCursor

sys.path.extend(["F:/ML/Fortune", "D:/Fortune", "/www/wwwroot/Fortune"])
from DataBase import DatabaseConsts
import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection
from DBUtils.PersistentDB import PersistentDB, PersistentDBError, NotSupportedError


class DB_POOL():
    host = '192.168.0.199'
    port = 3306
    user = 'root'
    password = 'root'

    @classmethod
    def get_db_pool(cls, db, host=host, port=port, user=user, passwd=password, charset='utf8', cursor_class =  DictCursor, is_multi_thread=True):
        config = {
            'host': host,
            'port': int(port),
            'database': db,
            'user': user,
            'password': passwd,
            'charset': charset,
            'cursorclass' : cursor_class
        }

        if is_multi_thread:
            poolDB = PooledDB(
                # 指定数据库连接驱动
                creator=pymysql,
                # 连接池允许的最大连接数,0和None表示没有限制
                maxconnections=9,
                # 初始化时,连接池至少创建的空闲连接,0表示不创建
                mincached=6,
                # 连接池中空闲的最多连接数,0和None表示没有限制
                maxcached=15,
                # 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
                maxshared=9,
                # 连接池中如果没有可用共享连接后,是否阻塞等待,True表示等等,
                # False表示不等待然后报错
                blocking=True,
                # 开始会话前执行的命令列表
                setsession=[],
                # ping Mysql服务器检查服务是否可用
                ping=1,
                **config
            )
        else:
            poolDB = PersistentDB(
                # 指定数据库连接驱动
                creator=pymysql,
                # 一个连接最大复用次数,0或者None表示没有限制,默认为0
                maxusage=1000,
                **config
            )
        return poolDB


if __name__ == '__main__':
    # 以单线程的方式初始化数据库连接池
    db_pool = DB_POOL.get_db_pool(is_multi_thread=True, db="marketstatistic", host ="localhost", passwd="h303567469")
    # 从数据库连接池中取出一条连接
    conn = db_pool.connection()
    try:
        for i in range(3):
            with conn.cursor() as cursor:
            # cursor = conn.cursor()
                # 随便查一下吧
                cursor.execute('select * from market_temperature_auto')
                # 随便取一条查询结果
                result = cursor.fetchone()
                print(result)
                time.sleep(1)
            print("conn is close: {}".format(conn._closed))

        # 把连接返还给连接池
    finally:
        conn.close()
        # print("conn is close: {}".format(conn._closed))


# class DBConnection:
#     host = '192.168.0.199'
#     # host = '127.0.0.1'
#     port = 3306
#     db = DatabaseConsts.StockDdataOverDB
#     user = 'root'
#     # password = 'h303567469'
#     password = 'root'
#
#     def __init__(self, db, host=host, port=port, user=user, passwd=password, charset='utf8',
#                  cursor_type=pymysql.cursors.DictCursor):
#         self.connection = pymysql.connect(host=host,
#                                           port=int(port),
#                                           user=user,
#                                           password=passwd,
#                                           db=db,
#                                           charset='utf8')
#         self.cursor = self.connection.cursor(cursor=cursor_type)
#
#         def keep_connection(self):
#             print("args :{}".format(self))
#             sql = " select 1 ;"
#             self.cursor.execute(sql)
#             self.cursor.fetchall()
#             print("keep connection ")
#             # 初始化 sched 模块的 scheduler 类
#
#         scheduler = BackgroundScheduler()
#         # 添加调度任务
#         # 调度方法为 timedTask，触发器选择 interval(间隔性)，间隔时长为 2 秒
#         scheduler.add_job(keep_connection, trigger='interval', args=(self,), seconds=60 * 2)
#         # 启动调度任务
#         scheduler.start()
#
#     @staticmethod
#     def get_mysql_db_uri(host,
#                          user,
#                          password,
#                          db,
#                          port=3306,
#                          charset='utf8'):
#         db_uri = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8". \
#             format(username=user, password=password, host=host, port=port, db=db)
#         return db_uri
